Scottish Snow

Mike Spencer
2016-06-15

Overview

  • Scottish snow
  • Databases
  • Parallel processing
  • Results

Why is snow important?

  • Visible indicator
  • Water resources
  • Tourism
  • Climate feedback

Why is snow important?

  • Forecasting snow cover?
  • Snowmelt frequency?
  • Snowcover frequency?

Snow survey of Great Britain

  • Collected by volunteers
  • 1945 to 2007 (ish)
  • 140 sites across Scotland
  • Subjective assessment of snowline

Snow survey of Great Britain

alt text

Snow survey of Great Britain

alt text

Snow survey of Great Britain

alt text

Snow survey of Great Britain

alt text

Databases

alt text

Databases

  • Requires tidy data
  • Platform/software independant
  • New language
  • Offload heavy lifting

Databases

Preamble

install.packages("RSQLite")
library(RSQLite)

Connection

db = dbConnect(SQLite(), "~/Cloud/Michael/Uni_temp/SSGB/SSGB.sqlite")

You're now good to go!

Databases basics

# Available tables
dbListTables(db)

# Columns in a table
dbListFields(db, "SSGB_stations")

Databases queries

# Read all columns from obs table from Aviemore
head(dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore'"))

head(dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore' AND Snowline!='m'"))

df = dbGetQuery(db, "SELECT * FROM SSGB_obs WHERE Station='Aviemore' AND Snowline!='m'")

Databases queries

# lapply multiple stations
st = c("Aviemore", "Eskdalemuir", "Dalwhinnie")

dl = lapply(seq_along(st), function(i){
   dbGetQuery(db, paste0("SELECT HydroYear, Station, SnowlineElev, COUNT(SnowlineElev) AS Days FROM SSGB_obs WHERE Station='", st[i], "' GROUP BY HydroYear, Station, SnowlineElev"))
})

Databases queries

# Temp tables
dbSendQuery(db, "
   CREATE TEMP TABLE step AS
   SELECT * FROM SSGB_obs
   WHERE strftime('%m', Date) BETWEEN '11' AND '12'
      AND SnowlineElev !='m'
   OR strftime('%m', Date) BETWEEN '01' AND '04'
      AND SnowlineElev !='m'
")

Database writing

# Create a new table
dbSendQuery(db,
      "CREATE TABLE fielddata
         (HydroYear INT,
         Date DATETIME,
         Station TEXT,
         Snowline TEXT,
         SnowlineElev TEXT,
         PRIMARY KEY(Date, Station))
")

# Write to it
dbWriteTable(conn=db, name="SSGB_obs", new.data, append=T, row.names=F)

Database resources

Parallel processing

Parallel is available in R core

library(parallel)

Parallel processing

mclapply()

Parallel processing

# parameters
install.packages("TunePareto")
library(TunePareto)

params = list(
   a = 1:10,
   b = seq(0.1, 1, by=0.1))

# How many iterations?
prod(sapply(params, length))

# Get all parameter combinations
params = allCombinations(params)
params = lapply(params, function(i){
   do.call("cbind.data.frame", i)
})
params = do.call("rbind.data.frame", params)

Parallel processing

# Single core
ptm = proc.time()
dl = lapply(1:nrow(params), function(i){
   rnorm(n=10, mean=params[i, 1], sd=params[i, 2])
})
proc.time() - ptm

# Multi core
ptm = proc.time()
dl = mclapply(1:nrow(params), mc.cores=2, function(i){
   rnorm(n=10, mean=params[i, 1], sd=params[i, 2])
})
proc.time() - ptm

Results

alt text

Results

alt text

Results

alt text

Results

alt text

Summary

Any questions?